/*******************************************************************************
 * Copyright (c) 2010, 2030 www.itlaobing.cn
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package cn.itlaobing.dao;/**
 * Created by asus-pc on 2017/11/14 0014.
 */

import cn.itlaobing.entity.GoodsDTO;
import cn.itlaobing.entity.Itlb_focuss;
import cn.itlaobing.entity.NewGoodsDTO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * ClassName: GoodsDao <br/>
 * Function: TODO 功能描述 <br/>
 * date: 2017/11/14 0014 10:51 <br/>
 *
 * @author 卖姑娘的小柴火
 * @since JDK 1.8
 */
public class GoodsDao extends BaseDao {
    public List<GoodsDTO> checkgoods()throws SQLException{
        String sql="SELECT g.id,g.price,g.good_name,p.picUrl FROM itlb_goods g LEFT JOIN " +
                "(SELECT good_id,SUM(buy_count)sales_count FROM itlb_order_details " +
                "GROUP BY good_id)s ON g.id=s.good_id LEFT JOIN itlb_goods_pic p ON g.id=p.goodsId " +
                "WHERE g.status_line=1 ORDER BY s.sales_count DESC";
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<GoodsDTO> beanListHandler=new BeanListHandler<GoodsDTO>(GoodsDTO.class);
        return qr.query(sql,beanListHandler);
    }

    public List<NewGoodsDTO> checkByNewGoods()throws SQLException{
        String sql="SELECT s.id,s.price,s.good_name,p.picUrl  FROM itlb_goods s " +
                "LEFT JOIN itlb_goods_pic p ON s.id=p.goodsId WHERE s.good_new =1";
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<NewGoodsDTO> beanListHandler=new BeanListHandler<NewGoodsDTO>(NewGoodsDTO.class);
        return qr.query(sql,beanListHandler);
    }

    public List<Itlb_focuss> focuss()throws SQLException{
        String sql="SELECT * FROM itlb_focuss";
        QueryRunner queryRunner=new QueryRunner(getDataSource());
        BeanListHandler<Itlb_focuss> beanListHandler =new BeanListHandler<Itlb_focuss>(Itlb_focuss.class);
        return queryRunner.query(sql,beanListHandler);
    }
}
